﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using eProject_SEM3_SecurityServices.Bussiness;
using eProject_SEM3_SecurityServices.ObjectClass;
using System.Data;
using System.Data.SqlClient;


namespace eProject_SEM3_SecurityServices.DataAccess
{
    public class DEmployee:DConnectSQL
    {
        
        public List<OEmployee> GetEmployeeList()
        {
            List<OEmployee> listEmployees = null;

            //Lets get the list of all employees in a datataable
            using (DataTable table = DConnectSQL.ExecuteSelectCommand("GetEmployeeList", CommandType.StoredProcedure))
            {
                //check if any record exist or not
                if (table.Rows.Count > 0)
                {
                    //Lets go ahead and create the list of employees
                    listEmployees = new List<OEmployee>();

                    //Now lets populate the employee details into the list of employees
                    foreach (DataRow row in table.Rows)
                    {
                        OEmployee employee = new OEmployee();
                        employee.EmployeeID = Convert.ToInt32(row["employeeId"]);
                        employee.UserName = row["UserName"].ToString();
                        employee.Role = Convert.ToInt32(row["Role"]);
                        employee.EmployeeFullName = row["EmployeeFullName"].ToString();
                        employee.EmployeeBirthday = row["EmployeeBirthday"].ToString();
                        employee.EmployeeSalary = row["EmployeeSalary"].ToString();
                        employee.EmployeeGender = Convert.ToBoolean(row["EmployeeGender"]);
                        employee.Region = row["Region"].ToString();
                        employee.EmployeeAddress = row["EmployeeAddress"].ToString();
                        employee.DepartmentId = Convert.ToInt32(row["DepartmentId"]);
                        employee.EmployeePhone = Convert.ToInt32(row["empoyeePhone"]);

                        listEmployees.Add(employee);
                    }
                }
            }

            return listEmployees;
        }
        public OEmployee GetEmployeeDetails(int empID)
        {
            OEmployee employee = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@empId", empID)
            };
            //Lets get the list of all employees in a datataable
            using (DataTable table = DConnectSQL.ExecuteParamerizedSelectCommand("GetEmployeeDetails", CommandType.StoredProcedure, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of employees
                    employee = new OEmployee();

                    //Now lets populate the employee details into the list of employees                                           
                    employee.EmployeeID = Convert.ToInt32(row["employeeId"]);
                    employee.Password = row["password"].ToString();
                    employee.UserName = row["UserName"].ToString();
                    employee.Role = Convert.ToInt32(row["Role"]);
                    employee.EmployeeFullName = row["EmployeeFullName"].ToString();
                    employee.EmployeeBirthday = row["EmployeeBirthday"].ToString();
                    employee.EmployeeSalary = row["EmployeeSalary"].ToString();
                    employee.EmployeeGender = Convert.ToBoolean(row["EmployeeGender"]);
                    employee.Region = row["Region"].ToString();
                    employee.EmployeeAddress = row["EmployeeAddress"].ToString();
                    employee.DepartmentId = Convert.ToInt32(row["DepartmentId"]);
                    employee.EmployeePhone = Convert.ToInt32(row["empoyeePhone"]);
                }
            }
            return employee;
        }

        public bool AddNewEmployee(OEmployee employee)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {                
                new SqlParameter("@EmployeeFullName", employee.EmployeeFullName),
                new SqlParameter("@UserName", employee.UserName),
                new SqlParameter("@Password", employee.Password),
                new SqlParameter("@EmployeeAddress", employee.EmployeeAddress),
                new SqlParameter("@EmployeeBirthday", employee.EmployeeBirthday),
                new SqlParameter("@EmployeeGender", employee.EmployeeGender),
                new SqlParameter("@EmployeeSalary", employee.EmployeeSalary),
                new SqlParameter("@EployeePhone", employee.EmployeePhone),
                new SqlParameter("@Role", employee.Role),
                new SqlParameter("@Region", employee.Region),
                new SqlParameter("@DepartmentId", employee.DepartmentId)
            };

            return DConnectSQL.ExecuteNonQuery("AddNewEmployee", CommandType.StoredProcedure, parameters);
        }

        public bool UpdateEmployee(OEmployee employee)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@EmployeeId",employee.EmployeeID),
                new SqlParameter("@EmployeeFullName", employee.EmployeeFullName),
                new SqlParameter("@UserName", employee.UserName),
                new SqlParameter("@Password", employee.Password),
                new SqlParameter("@EmployeeAddress", employee.EmployeeAddress),
                new SqlParameter("@EmployeeBirthday", employee.EmployeeBirthday),
                new SqlParameter("@EmployeeGender", employee.EmployeeGender),
                new SqlParameter("@EmployeeSalary", employee.EmployeeSalary),
                new SqlParameter("@EployeePhone", employee.EmployeePhone),
                new SqlParameter("@Role", employee.Role),
                new SqlParameter("@Region", employee.Region),
                new SqlParameter("@DepartmentId", employee.DepartmentId)
            };

            return DConnectSQL.ExecuteNonQuery("UpdateEmployee", CommandType.StoredProcedure, parameters);
        }

        public bool DeleteEmployee(int empID)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@empId", empID)
            };

            return DConnectSQL.ExecuteNonQuery("DeleteEmployee", CommandType.StoredProcedure, parameters);
        }
        public List<OEmployee> getEmployeeContract(int departmentId, int numberEmployee)
        {           
            List<OEmployee> listEmployees = null;
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@departmentId", departmentId),
                new SqlParameter("@numberEmployee", numberEmployee)
            };
            //Lets get the list of all employees in a datataable
            using (DataTable table = DConnectSQL.ExecuteParamerizedSelectCommand("GetEmployeeContract", CommandType.StoredProcedure, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count > 0)
                {
                    //Lets go ahead and create the list of employees
                    listEmployees = new List<OEmployee>();

                    //Now lets populate the employee details into the list of employees
                    foreach (DataRow row in table.Rows)
                    {
                        OEmployee employee = new OEmployee();
                        employee.EmployeeID = Convert.ToInt32(row["employeeId"]);
                        employee.UserName = row["UserName"].ToString();
                        employee.Role = Convert.ToInt32(row["Role"]);
                        employee.EmployeeFullName = row["EmployeeFullName"].ToString();
                        employee.EmployeeBirthday = row["EmployeeBirthday"].ToString();
                        employee.EmployeeSalary = row["EmployeeSalary"].ToString();
                        employee.EmployeeGender = Convert.ToBoolean(row["EmployeeGender"]);
                        employee.Region = row["Region"].ToString();
                        employee.EmployeeAddress = row["EmployeeAddress"].ToString();
                        employee.DepartmentId = Convert.ToInt32(row["DepartmentId"]);
                        employee.EmployeePhone = Convert.ToInt32(row["empoyeePhone"]);

                        listEmployees.Add(employee);
                    }
                }
            }

            return listEmployees;
        }
    }
}